package music.dao;

import music.model.BoardVO;
import music.utils.dbcp.OracleDBCP;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

public class BoardDAO {

    private static String listBoard = "select * from listboard where page = ?";
    private static String countBoard = "select ceil(count(bdno)/?) as pages from board";
    private static String writeBoard = "insert into board(bdno, bdtitle, userid, contents) values (bdno_seq.nextval, ?, ?, ?)";
    private static String selectOneSQL = "select * from board where bdno=?";
    private static String hitOneSQL = "update board set reades = reades+1 where bdno = ?";
    private static String updateBoard = "update board set bdtitle = ?, contents = ?, regdate=sysdate where bdno = ?";
    private static String deleteBoard = "delete from board where bdno=?";


    public static ArrayList<BoardVO> listBoard(String cpage) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        ArrayList<BoardVO> lists = new ArrayList<>();

        try {
            conn = OracleDBCP.openDBCP();
            ps = conn.prepareStatement(listBoard);
            ps.setString(1, cpage);

            rs = ps.executeQuery();

            while (rs.next()) {
                BoardVO bvo = new BoardVO();
                bvo.setBdno(rs.getString(1));
                bvo.setBdtitle(rs.getString(2));
                bvo.setUserid(rs.getString(3));
                bvo.setRegdate(rs.getString(4));
                bvo.setReades(rs.getString(5));

                lists.add(bvo);
            }


        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            OracleDBCP.closeDBCP(conn, ps, rs);
        }


        return lists;
    }


    public static int countBoard(int perPage) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        int pages = 1;

        try {
            conn = OracleDBCP.openDBCP();
            ps = conn.prepareStatement(countBoard);
            ps.setInt(1, perPage);
            rs = ps.executeQuery();

            if (rs.next())
                pages = rs.getInt(1);


            //select ceil ( count(bdno) / 15) as pages from board
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            OracleDBCP.closeDBCP(conn, ps, rs);
        }

        return pages;
    }

    public static boolean writeBoard(BoardVO bvo) {
        Connection conn = null;
        PreparedStatement ps = null;
        boolean isWrite = false;

        try {
            conn = OracleDBCP.openDBCP();
            ps = conn.prepareStatement(writeBoard);
            ps.setString(1, bvo.getBdtitle());
            ps.setString(2, bvo.getUserid());
            ps.setString(3, bvo.getContents());

            if (ps.executeUpdate() > 0) isWrite = true;

        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            OracleDBCP.closeDBCP(conn, ps, null);
        }

        return isWrite;
    }

    public static BoardVO selectView(String bdno) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        BoardVO bvo = null;

        try {
            conn = OracleDBCP.openDBCP();

            ps = conn.prepareStatement(hitOneSQL);
            ps.setString(1, bdno);
            ps.executeUpdate();


            ps = conn.prepareStatement(selectOneSQL);
            ps.setString(1, bdno);
            rs = ps.executeQuery();

            while (rs.next()) {
                bvo = new BoardVO();
                bvo.setBdno(rs.getString(1));
                bvo.setBdtitle(rs.getString(2));
                bvo.setContents(rs.getString(3));
                bvo.setUserid(rs.getString(4));
                bvo.setRegdate(rs.getString(5));
                bvo.setReades(rs.getString(6));
            }

        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            OracleDBCP.closeDBCP(conn, ps, rs);
        }

        return bvo;

    }


    /*
       public static boolean hitView(String bdno) {
           Connection conn = null;
           PreparedStatement ps = null;
           boolean isHit = false;

           try {
               conn = OracleDBCP.openDBCP();
               ps = conn.prepareStatement(hitOneSQL);
               ps.setString(1, bdno);

               if(ps.executeUpdate() > 0 ) isHit = true;

           } catch (Exception ex) {
               ex.printStackTrace();
           } finally {
               OracleDBCP.closeDBCP(conn,ps,null);
           }

           return isHit;
       }
   */
    public static boolean updateBoard(BoardVO bvo) {
        Connection conn = null;
        PreparedStatement ps = null;
        boolean isUpdate = false;

        try {
            conn = OracleDBCP.openDBCP();
            ps = conn.prepareStatement(updateBoard);
            ps.setString(1, bvo.getBdtitle());
            ps.setString(2, bvo.getContents());
            ps.setString(3, bvo.getBdno());

            if (ps.executeUpdate() > 0)
                isUpdate = true;

        } catch (Exception ex) {
            System.out.println("updateBoard에서 오류 발생");
            ex.printStackTrace();

        } finally {
            OracleDBCP.closeDBCP(conn, ps, null);
        }
        return isUpdate;
    }


    public static boolean deleteBoard(String bdno) {
        Connection conn = null;
        PreparedStatement ps = null;
        boolean isDelete = false;

        try {
            conn = OracleDBCP.openDBCP();
            ps = conn.prepareStatement(deleteBoard);
            ps.setString(1, bdno);

            if (ps.executeUpdate() > 0) isDelete = true;

        } catch (Exception ex) {
            System.out.println("deleteBoard에서 오류 발생!!");
            ex.printStackTrace();
        } finally {
            OracleDBCP.closeDBCP(conn, ps, null);
        }
        return isDelete;
    }
}
